IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUsersById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUsersById]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************        
* Name:   [GetUsersById]        
* Purpose:  Retrieves matching user details filtered by admPersonIds.         
*        
* PARAMETERS(IN)        
* Name          Description             
* -------------      -------------------------------------------        
* @xml				xml Document containing list of Ids(REQUIRED)
* @referringTable	Refernce to the passed in datatable passed
					(This way it's possible to limit the retrieval to datagrid page records(default 20 numbers)
					or fetch the record for the whole of cached Ids).           
*********************************************************************/        
        
CREATE PROCEDURE [dbo].[GetUsersById]        
(        
@xml text,  
@referringTable int=0      
)        
AS BEGIN        
SET NOCOUNT ON        
        
 declare @xmlDoc int          
 declare @admPersonIds table (Sequence int identity, [ID] int)          
 exec sp_xml_preparedocument @xmlDoc output, @xml    
if @referringTable=0  
 begin        
  insert into @admPersonIds select [Id] from openxml(@xmlDoc, '//IdList', 2) with (Id int)  
 end   
else  
 begin  
  insert into @admPersonIds select [Id] from openxml(@xmlDoc, '//selectedIdsTable', 2) with (Id int)  
 end          
 exec sp_xml_removedocument @xmlDoc        
        
SELECT adp.[Id],        
       adp.[lastname],     
	   adp.[firstname],        
       fdu.[userName]as [UserName],    
	   fdo.[fullyQualifiedName]as [Organization]    
  FROM AdmPerson adp      
	Left JOIN FwkDomainUser fdu      
		ON adp.FwkDomainUserId = fdu.Id      
	Left JOIN AdmInternalPerson ip      
		ON adp.Id = ip.AdmPersonId       
	Left JOIN FwkDomainOrganization fdo      
		ON fdo.Id = ip.PrimaryOrganizationId     
	Inner JOIN @admPersonIds tempTable    
		ON adp.[Id]=tempTable.[Id]    
     
END  